DROP TABLE IF EXISTS users, categories, spending, trend_of_spending CASCADE;

CREATE TABLE IF NOT EXISTS users (
	id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name VARCHAR (50) NOT NULL,
	password VARCHAR NOT NULL,
	role VARCHAR NOT NULL,
	email VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS categories (
	id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name VARCHAR (50) NOT NULL,
	is_spending BOOLEAN NOT NULL,
	user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
	CONSTRAINT uq_name UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS trend_of_spending (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS spending (
	id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	created TIMESTAMP NOT NULL,
	category_id BIGINT REFERENCES categories (id) ON DELETE CASCADE,
	trend_id BIGINT REFERENCES trend_of_spending (id) ON DELETE CASCADE,
	note VARCHAR (200),
	quantity DOUBLE NOT NULL,
	user_id BIGINT REFERENCES users (id) ON DELETE CASCADE
);